package br.com.ac.fatureweb.service;

import java.util.List;

import javax.annotation.PostConstruct;
import javax.ejb.Local;
import javax.ejb.Stateless;
import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import br.com.ac.fatureweb.integration.enums.EnumSituacaoLigacao;
import br.com.ac.fatureweb.model.billing.Conta;
import br.com.ac.fatureweb.model.billing.LancamentoCobrado;
import br.com.ac.fatureweb.model.billing.LogLeitura;
import br.com.ac.fatureweb.model.billing.Produto;
import br.com.ac.fatureweb.model.billing.Valor;
import br.com.ac.fatureweb.model.billing.Volume;
import br.com.ac.fatureweb.model.carga.Debito;
import br.com.ac.fatureweb.model.carga.Economia;
import br.com.ac.fatureweb.model.carga.Historico;
import br.com.ac.fatureweb.model.carga.Imovel;
import br.com.ac.fatureweb.model.carga.LancamentoFuturo;
import br.com.ac.fatureweb.model.carga.SaldoDebitoCredito;
import br.com.ac.fatureweb.model.carga.Tarifa;
import br.com.ac.fatureweb.model.carga.TarifaSocial;
import br.com.ac.fatureweb.model.vo.RelatorioObservacaoCampoVO;
import br.com.ac.fatureweb.model.vo.RelatorioOcorrenciaVO;
import br.com.ac.fatureweb.model.vo.RelatorioResumoCampoVO;
import br.com.ac.fatureweb.utilitary.NgcDefaultBean;
import br.com.ac.fatureweb.view.RelatorioObservacaoCampoBean;
import br.com.ac.fatureweb.view.RelatorioOcorrenciaBean;
import br.com.ac.fatureweb.view.RelatorioResumoCampoBean;

@Local
@Named
@Stateless
public class NgcCadastroBean extends NgcDefaultBean<Imovel> {

	@PersistenceContext
	private EntityManager entityManager;

	@Inject
	private NgcLeituristaBean ngcLeituristaBean;

	@PostConstruct
	void inicialize() {
		setEm(entityManager);
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 29/01/2015
	 *
	 * @param inscricao
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Debito> getLstDebitos(Long inscricao, Long idCarga) {

		Query query = entityManager.createQuery("SELECT d FROM Debito d"
				+ " WHERE d.inscricao =:inscricao "
				+ "	AND d.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 02/02/2015
	 *
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Historico getHistorico(Long inscricao, Long idCarga) {

		Query query = entityManager.createQuery("SELECT h FROM Historico h"
				+ " WHERE h.inscricao =:inscricao "
				+ "	AND h.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		List<Historico> resultList = query.getResultList();
		if (resultList != null && !resultList.isEmpty())
			return resultList.get(0);

		return null;
	}

	/**
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 04/02/2015
	 *
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<LancamentoFuturo> getLstLancamentoFuturo(Long inscricao,
			Long idCarga) {

		Query query = entityManager
				.createQuery("SELECT lf FROM LancamentoFuturo lf"
						+ " WHERE lf.inscricao =:inscricao "
						+ "	AND lf.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 04/02/2015
	 *
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SaldoDebitoCredito> getLstSaldoDebitoCredito(Long inscricao,
			Long idCarga) {

		Query query = entityManager
				.createQuery("SELECT sdc FROM SaldoDebitoCredito sdc"
						+ " WHERE sdc.inscricao =:inscricao "
						+ "	AND sdc.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 04/02/2015
	 *
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<TarifaSocial> getLstTarifaSocial(Long inscricao, Long idCarga) {

		Query query = entityManager
				.createQuery("SELECT taf FROM TarifaSocial taf"
						+ " WHERE taf.inscricao =:inscricao "
						+ "	AND taf.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 04/02/2015
	 *
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Economia> getLstEconomia(Long inscricao, Long idCarga) {

		Query query = entityManager
				.createQuery("SELECT new Economia(eco.id, eco.codTarifa, eco.qtdEconomias, "
						+ " (SELECT tar.sigla FROM Tarifa tar WHERE tar.codigo = eco.codTarifa AND tar.carga = eco.carga) )  "
						+ "FROM Economia eco"
						+ " WHERE eco.inscricao =:inscricao "
						+ "	AND eco.carga.id = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 05/02/2015
	 * @param codigo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Tarifa getTarifa(Long codigo, Long idCarga) {
		Query query = entityManager
				.createQuery("FROM Tarifa taf INNER JOIN FETCH taf.lstFaixa fai "
						+ "WHERE taf.codigo = :codigo "
						+ "AND taf.carga.id = :idCarga");
		query.setParameter("codigo", codigo);
		query.setParameter("idCarga", idCarga);

		List<Tarifa> resultList = query.getResultList();
		if (resultList != null && !resultList.isEmpty())
			return resultList.get(0);

		return null;
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 03/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Conta getConta(Long inscricao, Long idCarga) {
		Query query = entityManager.createQuery("SELECT con FROM Conta con "
				+ "WHERE con.inscricao = :inscricao "
				+ "AND con.idCarga = :idCarga");
		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		List<Conta> resultList = query.getResultList();
		if (resultList != null && !resultList.isEmpty()) {
			Conta conta = resultList.get(0);
			conta.setLeiturista(ngcLeituristaBean.getLeituristaPorCodigo(conta
					.getCodLeiturista()));
			return conta;
		}

		return null;
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 03/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<LancamentoCobrado> getLstLancamentoCobrado(Long inscricao,
			Long idCarga) {

		Query query = entityManager
				.createQuery("SELECT lac FROM LancamentoCobrado lac"
						+ " WHERE lac.inscricao =:inscricao "
						+ "	AND lac.idCarga = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 03/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public LogLeitura getLogLeitura(Long inscricao, Long idCarga) {

		StringBuilder sb = new StringBuilder();
		sb.append("	SELECT new LogLeitura( ");
		sb.append("	 lol.seq, lol.id, lol.mes, lol.ano, lol.leitura, "
				+ "lol.dataLeitura, lol.valorConta, lol.qtdeImpressao, "
				+ "lol.leituraConfirmada, lol.codOcorrencia1, lol.idLeiturista, "
				+ "lol.inscricao, lol.idCarga, lol.observacao, "
				+ "lol.codOcorrencia2, lol.contaRetida, lol.contaRegerada, "
				+ "lol.foto, lol.longitude, lol.latitude, lol.consumo, "
				+ "lol.valorAgua, lol.valorEsgoto, lol.valorServicos, "
				+ "lol.valorTotal, lol.roteiro, ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia1),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia2)	 ");
		sb.append("	) ");

		sb.append("	FROM LogLeitura lol WHERE lol.inscricao = :inscricao "
				+ "AND lol.idCarga = :idCarga");

		Query query = entityManager.createQuery(sb.toString());

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		List<LogLeitura> resultList = query.getResultList();
		if (resultList != null && !resultList.isEmpty())
			return resultList.get(0);

		return null;
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 03/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Produto> getLstProduto(Long inscricao, Long idCarga) {

		Query query = entityManager.createQuery("SELECT pro FROM Produto pro"
				+ " WHERE pro.inscricao =:inscricao "
				+ "	AND pro.idCarga = :idCarga ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);

		return query.getResultList();
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 05/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @param codProduto
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Valor> getLstValor(Long inscricao, Long idCarga,
			Integer codProduto) {

		Query query = entityManager.createQuery("SELECT vlr FROM Valor vlr"
				+ " WHERE vlr.inscricao =:inscricao "
				+ "	AND vlr.idCarga = :idCarga "
				+ "	AND vlr.codProduto = :codProduto ");

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);
		query.setParameter("codProduto", codProduto);

		return query.getResultList();
	}

	/**
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 03/03/2015
	 * @param inscricao
	 * @param idCarga
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Volume getVolume(Long inscricao, Long idCarga, Integer codProduto) {

		StringBuilder sb = new StringBuilder();
		sb.append("	SELECT new Volume( ");
		sb.append("	vol.seq, vol.consumoFaturado, vol.consumoMedido, "
				+ " vol.codOcorrencia1, vol.codOcorrencia2, vol.consumoAguaMes,"
				+ " vol.inscricao, vol.idCarga, vol.codProduto,"
				+ " vol.leituraAtual, vol.leituraAnterior, vol.diasConsumo, ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = vol.codOcorrencia1),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = vol.codOcorrencia2)	 ");
		sb.append("	) ");

		sb.append("	FROM Volume vol   ");

		sb.append("		WHERE vol.inscricao =:inscricao "
				+ "	AND vol.idCarga = :idCarga "
				+ "	AND vol.codProduto = :codProduto ");

		Query query = entityManager.createQuery(sb.toString());

		query.setParameter("inscricao", inscricao);
		query.setParameter("idCarga", idCarga);
		query.setParameter("codProduto", codProduto);

		List<Volume> resultList = query.getResultList();
		if (resultList != null && !resultList.isEmpty())
			return resultList.get(0);

		return null;
	}

	/**
	 * {@link RelatorioOcorrenciaBean#gerarRelatorio()}
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 14/03/2015
	 *
	 * @param mes
	 * @param ano
	 * @param codDistrito
	 * @param codSetor
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<RelatorioOcorrenciaVO> getLstImoveisRelatorioOcorrencia(
			Integer mes, Integer ano, Long codDistrito, Long codSetor) {

		StringBuilder sb = new StringBuilder();
		sb.append("	SELECT new br.com.ac.fatureweb.model.vo.RelatorioOcorrenciaVO( ");
		sb.append("		imo, ");
		sb.append("		lol.leitura, ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = imo.ocorrenciaAnterior),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia1),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia2),	 ");
		sb.append("		lol.idLeiturista, ");
		sb.append("		lol.codOcorrencia1 ");
		sb.append("		) ");
		sb.append("		FROM Imovel imo, LogLeitura lol ");
		sb.append("		WHERE imo.inscricao = lol.inscricao ");
		sb.append("		 AND imo.carga.id = lol.idCarga ");

		if (mes != null)
			sb.append("		 AND imo.mes = :mes ");

		if (ano != null)
			sb.append("		 AND imo.ano = :ano ");

		if (codDistrito != null)
			sb.append("		 AND imo.codDistrito = :codDistrito ");

		if (codSetor != null)
			sb.append("		 AND imo.codSetor = :codSetor ");

		sb.append("		ORDER BY lol.idLeiturista,lol.codOcorrencia1 ");

		Query query = entityManager.createQuery(sb.toString());

		if (mes != null)
			query.setParameter("mes", mes);

		if (ano != null)
			query.setParameter("ano", ano);

		if (codDistrito != null)
			query.setParameter("codDistrito", codDistrito);

		if (codSetor != null)
			query.setParameter("codSetor", codSetor);

		return query.getResultList();
	}

	/**
	 * {@link RelatorioObservacaoCampoBean#gerarRelatorio()}
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 17/03/2015
	 * @param mes
	 * @param ano
	 * @param codDistrito
	 * @param codSetor
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<RelatorioObservacaoCampoVO> getLstImoveisRelatorioObservacaoCampo(
			Integer mes, Integer ano, Long codDistrito, Long codSetor) {

		StringBuilder sb = new StringBuilder();
		sb.append("	SELECT new br.com.ac.fatureweb.model.vo.RelatorioObservacaoCampoVO( ");
		sb.append("		imo, ");
		sb.append("		lol.leitura, ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = imo.ocorrenciaAnterior),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia1),	 ");
		sb.append("		(SELECT oco.descricao FROM Ocorrencia oco WHERE oco.codigo = lol.codOcorrencia2),	 ");
		sb.append("		lol.idLeiturista, ");
		sb.append("		lol.codOcorrencia1 ");
		sb.append("		) ");
		sb.append("		FROM Imovel imo, LogLeitura lol ");
		sb.append("		WHERE imo.inscricao = lol.inscricao ");
		sb.append("		 AND imo.carga.id = lol.idCarga ");

		if (mes != null)
			sb.append("		 AND imo.mes = :mes ");

		if (ano != null)
			sb.append("		 AND imo.ano = :ano ");

		if (codDistrito != null)
			sb.append("		 AND imo.codDistrito = :codDistrito ");

		if (codSetor != null)
			sb.append("		 AND imo.codSetor = :codSetor ");

		sb.append("		ORDER BY lol.idLeiturista,lol.codOcorrencia1 ");

		Query query = entityManager.createQuery(sb.toString());

		if (mes != null)
			query.setParameter("mes", mes);

		if (ano != null)
			query.setParameter("ano", ano);

		if (codDistrito != null)
			query.setParameter("codDistrito", codDistrito);

		if (codSetor != null)
			query.setParameter("codSetor", codSetor);

		return query.getResultList();
	}

	/**
	 * {@link RelatorioResumoCampoBean#gerarRelatorio()}
	 *
	 * @author Brunno Gonçalves (brunnogoncalves@gmail.com) 27/03/2015
	 * @param mes
	 * @param ano
	 * @param codDistrito
	 * @param codSetor
	 * @return
	 */
	public List<RelatorioResumoCampoVO> getDadosRelatorioResumoCampo(
			Integer mes, Integer ano, Long codDistrito, Long codSetor) {

		StringBuilder sb = new StringBuilder();
		sb.append("	SELECT new br.com.ac.fatureweb.model.vo.RelatorioResumoCampoVO( ");

		// Visitas programadas
		sb.append("		COUNT(imo), ");

		// Medidos (temHidrometro TRUE)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo AND imoMedMed.temHidrometro IS TRUE), ");

		// Leitura (com LogLeitura)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao AND lol.idCarga = imoMedMed.carga.id )), ");

		// Ocorrência (ocorrencia1 do logleitura != null)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao "
				+ "							AND lol.idCarga = imoMedMed.carga.id"
				+ "							AND lol.codOcorrencia1 IS NOT NULL	 )), ");

		// Não Medidos (temHidrometro FALSE)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo AND imoMedMed.temHidrometro IS FALSE), ");

		// Não Medidos com Leitura (com LogLeitura)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS FALSE"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao AND lol.idCarga = imoMedMed.carga.id )), ");

		// Não medidos com Ocorrência (ocorrencia1 do logleitura != null)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS FALSE"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao "
				+ "							AND lol.idCarga = imoMedMed.carga.id"
				+ "							AND lol.codOcorrencia1 IS NOT NULL	 )), ");

		// Medidos Ligados (temHidrometro TRUE - situacaoImovel: LIGADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo AND imoMedMed.temHidrometro IS TRUE "
				+ "			AND imoMedMed.situacao = :situacaoLigado  ), ");

		// Ligados com Leitura (com LogLeitura - situacaoImovel: LIGADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE  "
				+ "			AND imoMedMed.situacao = :situacaoLigado 	"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao AND lol.idCarga = imoMedMed.carga.id )), ");

		// Ligados com Ocorrência (ocorrencia1 do logleitura != null -
		// situacaoImovel: LIGADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE"
				+ "			AND imoMedMed.situacao = :situacaoLigado 	"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao "
				+ "							AND lol.idCarga = imoMedMed.carga.id"
				+ "							AND lol.codOcorrencia1 IS NOT NULL	 )), ");

		// Medidos Cortados (temHidrometro TRUE - situacaoImovel: CORTADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE "
				+ "			AND imoMedMed.situacao = :situacaoCortado  ), ");

		// Cortados com Leitura (com LogLeitura - situacaoImovel: CORTADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE  "
				+ "			AND imoMedMed.situacao = :situacaoCortado 	"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao AND lol.idCarga = imoMedMed.carga.id )), ");

		// Cortados com Ocorrência (ocorrencia1 do logleitura != null -
		// situacaoImovel: CORTADO)
		sb.append("		(SELECT COUNT(imoMedMed) FROM Imovel imoMedMed WHERE imoMedMed = imo "
				+ "			AND imoMedMed.temHidrometro IS TRUE"
				+ "			AND imoMedMed.situacao = :situacaoCortado 	"
				+ "			AND EXISTS(select 1 from LogLeitura lol "
				+ "						WHERE lol.inscricao = imoMedMed.inscricao "
				+ "							AND lol.idCarga = imoMedMed.carga.id"
				+ "							AND lol.codOcorrencia1 IS NOT NULL	 )) ");

		sb.append("		) ");

		sb.append("		FROM Imovel imo ");

		sb.append("		WHERE 1=1 ");

		if (mes != null)
			sb.append("		 AND imo.mes = :mes ");

		if (ano != null)
			sb.append("		 AND imo.ano = :ano ");

		if (codDistrito != null)
			sb.append("		 AND imo.codDistrito = :codDistrito ");

		if (codSetor != null)
			sb.append("		 AND imo.codSetor = :codSetor ");

		sb.append("		GROUP BY imo.id ");

		Query query = entityManager.createQuery(sb.toString());

		if (mes != null)
			query.setParameter("mes", mes);

		if (ano != null)
			query.setParameter("ano", ano);

		if (codDistrito != null)
			query.setParameter("codDistrito", codDistrito);

		if (codSetor != null)
			query.setParameter("codSetor", codSetor);

		query.setParameter("situacaoLigado", EnumSituacaoLigacao.LIGADO);
		query.setParameter("situacaoCortado", EnumSituacaoLigacao.CORTADA);

		return query.getResultList();
	}

}
